ALTER                     function fn_change_orders_display (@salesorder_id int)
returns  @fn_result Table(SalesOrder_ID int,
	LineItem_Id int,
	COOL_Line_Item_No int,
	COOL_PO_Line_Item_No int, -- Added by Basha (08/18/2003)
	Product_ID int,
	-- Sharath on 4th July 03.
 	COOL_Description varchar(500),
	-- Ends Here.
	COOL_Qty int,
        COOL_Unit_Cost float,
        COOL_Amount float,
        COOL_Requested_Date datetime,
	COOL_Sugg_Schedule_Date_With_Substitutables datetime,
        COOL_Sugg_Schedule_Date_Without_Substitutables datetime,
	COOL_Schedule_Date datetime,
	COOL_Revised_Date datetime,
        COOL_Status varchar(50),
        COOL_Taxable int,
        COOL_Is_Cancel int,
        SalesOrder_No varchar(50),
        Final_Cool_Status varchar(50),
	Issue_Status varchar(50),
	Customer_PartNumber varchar(50), --Added by sarada
	Label_Number varchar(50), -- Added by varma (18 Nov 2004)
	COOL_Is_Expedite int,
	COOL_CUSTOM_PRICE_FIELD1 float, 
	COOL_CUSTOM_TEXT_FIELD1 varchar(1000))
	
as
begin
	declare @sales_order_id int
	declare @line_item_id int
        declare @cool_line_item_no int
	declare @cool_po_line_item_no int -- Added by Basha (08/18/2003)
	declare @temp_product_id int
	-- ** Sharath on 4th July 03.
	declare @temp_cool_description varchar(500)
	-- ** Ends Here.
	declare @temp_cool_qty int
	declare @temp_cool_unit_cost float
	declare @temp_cool_amount float
        declare @temp_cool_requested_date datetime
	declare @temp_cool_sugg_date_with_substitutables datetime
	declare @temp_cool_sugg_date_without_substitutables datetime
	declare @temp_cool_schedule_date datetime
	declare @temp_cool_revised_date datetime
	declare @temp_cool_status varchar(50)
	declare @temp_cool_taxable int
        declare @sales_order_no varchar(50)
	declare @temp_cool_is_cancel int
       	declare @final_cool_status varchar(50)
       	declare @cslit_status varchar(50)
	declare @simplecount int
	declare @customer_partnumber varchar(50)
	declare @label_number varchar(50)
	declare @customer_id int
	declare @odm_id int   ----Added by varma		
	declare @cool_is_expedite int
	DECLARE @COOL_CUSTOM_PRICE_FIELD1 float
	DECLARE @COOL_CUSTOM_TEXT_FIELD1 varchar(1000)
	     
	declare @open_orders_cursor CURSOR
	declare @approved_orders_cursor CURSOR
       	declare @status_cursor CURSOR
	declare @result Table(SalesOrder_ID int,
			LineItem_Id int,
			COOL_Line_Item_No int,
			COOL_PO_Line_Item_No int, -- Added by Basha (08/18/2003)
			Product_ID int,
			-- ** Sharath on 4th Jult 03.
			COOL_Description varchar(500),
			-- ** Ends Here.
			COOL_Qty int,
			
                	COOL_Unit_Cost float,
		        COOL_Amount float,
                   	COOL_Requested_Date datetime,
			COOL_Sugg_Schedule_Date_With_Substitutables datetime,
                        COOL_Sugg_Schedule_Date_Without_Substitutables datetime,
			COOL_Schedule_Date datetime,
			COOL_Revised_Date datetime,
                      	COOL_Status varchar(50),
                      	COOL_Taxable int,
                      	COOL_Is_Cancel int,
                      	SalesOrder_No varchar(50),
                      	Final_Cool_Status varchar(50),
			Issue_Status varchar(50),
			Customer_PartNumber varchar(50),
			Label_Number varchar(50), --added by varma
			COOL_Is_Expedite int,
			COOL_CUSTOM_PRICE_FIELD1 float, 
			COOL_CUSTOM_TEXT_FIELD1 varchar(1000))
			
			SELECT @customer_id = Customer_Id, @odm_id = SO_ODM_Id from Sales_Order where SalesOrder_Id = @salesorder_id
			
			SET @open_orders_cursor = CURSOR FOR 
			    SELECT Sales_Order.SalesOrder_ID ,LineItem_Id ,COOL_Line_Item_No , COOL_PO_Line_Item_No, Product_ID ,COOL_Description ,COOL_Qty ,COOL_Unit_Cost ,
			COOL_Amount,COOL_Requested_Date ,COOL_Sugg_Schedule_Date_With_Substitutables ,
			COOL_Sugg_Schedule_Date_Without_Substitutables ,COOL_Schedule_Date ,COOL_Revised_Date ,
			COOL_Status ,COOL_Taxable,COOL_Is_Cancel ,Sales_Order.SalesOrder_No, COOL_Is_Expedite, COOL_CUSTOMER_PARTNUMBER, COOL_CUSTOM_PRICE_FIELD1, COOL_CUSTOM_TEXT_FIELD1
			FROM Customer_Open_Order_LineItem ,Sales_Order WHERE Customer_Open_Order_LineItem.SalesOrder_Id = @salesorder_id 
			 and  Customer_Open_Order_LineItem.SalesOrder_Id = Sales_Order.SalesOrder_Id  and COOL_Status<> 'APPROVED'
			
			SET @approved_orders_cursor = CURSOR FOR 
			    SELECT Sales_Order.SalesOrder_ID ,LineItem_Id,COOL_Line_Item_No, COOL_PO_Line_Item_No ,Product_ID ,COOL_Description, COOL_Qty ,COOL_Unit_Cost ,
			COOL_Amount ,COOL_Requested_Date ,COOL_Sugg_Schedule_Date_With_Substitutables,
			COOL_Sugg_Schedule_Date_Without_Substitutables ,COOL_Schedule_Date ,COOL_Revised_Date ,
			COOL_Status,COOL_Taxable ,COOL_Is_Cancel,Sales_Order.SalesOrder_No , COOL_Is_Expedite, COOL_CUSTOMER_PARTNUMBER, COOL_CUSTOM_PRICE_FIELD1, COOL_CUSTOM_TEXT_FIELD1
			FROM Customer_Open_Order_LineItem,Sales_Order WHERE Customer_Open_Order_LineItem.SalesOrder_Id = @salesorder_id 
			and Customer_Open_Order_LineItem.SalesOrder_Id = Sales_Order.SalesOrder_Id and COOL_Status = 'APPROVED'
			
				open @open_orders_cursor		
				fetch next from @open_orders_cursor into @sales_order_id,@line_item_id , @cool_line_item_no, @cool_po_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
				@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,@temp_cool_sugg_date_without_substitutables,
				@temp_cool_schedule_date ,@temp_cool_revised_date, @temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,
				@sales_order_no , @cool_is_expedite, @customer_partnumber, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1
				
				-- Vasanth 08/28/2003 --begin
				-- Comments:: please dont use @for the cursor declaration unless ur using it as a variable else where
				-- code modified here. 
				-- one fetch statement was enough 
				-- the cursor was not closed either 
				-- Vasanth 08/28/2003 --end
				        while @@fetch_status = 0
					begin
					--Added by sarada
					--set @customer_partnumber = null
					set @label_number = null
					--select @customer_partnumber = customer_partnumber from Customer_PartNumbers where product_id = @temp_product_id and Customer_ID = @customer_id
					select @label_number = Label_Number from Customer_PartNumbers where product_id = @temp_product_id and Customer_ID = @customer_id and ODM_Id = @odm_id
				
						if(  @temp_cool_status = 'CANCELLED' ) --if4
					        	begin
					        		insert  @result values ( @sales_order_id,@line_item_id , @cool_line_item_no, @cool_po_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
							@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
							@temp_cool_sugg_date_without_substitutables,@temp_cool_schedule_date ,@temp_cool_revised_date,
							@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,@temp_cool_status,'NOT ISSUED',@customer_partnumber,@label_number,@cool_is_expedite, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1)
								
					        	end --if4
 
					         	if(  @temp_cool_status = 'SHIPPED' ) --if1
					         	begin
					        		insert  @result values ( @sales_order_id,@line_item_id , @cool_line_item_no,@cool_po_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
							@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
							@temp_cool_sugg_date_without_substitutables,@temp_cool_schedule_date ,@temp_cool_revised_date,
							@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,@temp_cool_status,'NOT ISSUED',@customer_partnumber,@label_number,@cool_is_expedite, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1)
					        	end --if1
        
        					 	if(  @temp_cool_status = 'HOLD' ) --if2
					         	begin
								
								declare @shipped_qty int 
								declare @partially_status varchar(50)
								set @partially_status = @temp_cool_status
								select @shipped_qty = isnull(sum(cslit_qty_shipped),0) from customer_shipping_line_item_table 
								where cslit_status='SHIPPED'and LineItem_ID = @line_item_id	
					
								if @shipped_qty > 0
								begin
									set @partially_status = 'PARTIALLY SHIPPED'
								end
									
								
					        		insert  @result values ( @sales_order_id,@line_item_id , @cool_line_item_no, @cool_po_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
							@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
							@temp_cool_sugg_date_without_substitutables,@temp_cool_schedule_date ,@temp_cool_revised_date,
							@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,@partially_status,'NOT ISSUED',@customer_partnumber,@label_number,@cool_is_expedite, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1)
	
					         	end --if2
					  	if(  @temp_cool_status = 'PENDING' ) --if3
						begin
	        						insert  @result values ( @sales_order_id,@line_item_id , @cool_line_item_no, @cool_po_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
							@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
							@temp_cool_sugg_date_without_substitutables,@temp_cool_schedule_date ,@temp_cool_revised_date,
							@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,@temp_cool_status,'NOT ISSUED',@customer_partnumber,@label_number,@cool_is_expedite, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1)
	
						end --if3
						
						fetch next from @open_orders_cursor into @sales_order_id,@line_item_id , @cool_line_item_no,@cool_po_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
						@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,@temp_cool_sugg_date_without_substitutables,
						@temp_cool_schedule_date ,@temp_cool_revised_date, @temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,
						@sales_order_no,@cool_is_expedite, @customer_partnumber, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1
						--SELECT @customer_partnumber = Customer_PartNumber from Customer_PartNumbers where Product_Id = @temp_product_id and Customer_Id = @customer_id		
					       end --while
		close @open_orders_cursor
		deallocate @open_orders_cursor
        	open @approved_orders_cursor		
			fetch next from @approved_orders_cursor into @sales_order_id,@line_item_id , @cool_line_item_no,@cool_po_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
		@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,@temp_cool_sugg_date_without_substitutables,
		@temp_cool_schedule_date ,@temp_cool_revised_date, @temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,
		@sales_order_no,@cool_is_expedite, @customer_partnumber, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1
		
		while @@fetch_status = 0
		begin
		-- Added by sarada 
		--set @customer_partnumber = null
		set @label_number = null
		--select @customer_partnumber = customer_partnumber from Customer_PartNumbers where product_id = @temp_product_id and Customer_ID = @customer_id
		select @label_number = Label_Number from Customer_PartNumbers where product_id = @temp_product_id and Customer_ID = @customer_id and ODM_Id = @odm_id
			
       	        declare @cslit_status1 varchar(50)
/* Modified By Basha inorder to get status for a line item */
       
	   SET @status_cursor = CURSOR FOR 
	   SELECT distinct cslit_status FROM Customer_Shipping_Line_Item_Table WHERE LineItem_Id = @line_item_id and cslit_status =(select max(cslit_status) from customer_shipping_line_item_table where lineitem_id = @line_item_id )
	   --SELECT cslit_status FROM Customer_Shipping_Line_Item_Table WHERE LineItem_Id = @line_item_id and cslit_id=(select max(cslit_id) from customer_shipping_line_item_table where lineitem_id = @line_item_id )
           --SELECT cslit_status FROM Customer_Shipping_Line_Item_Table WHERE LineItem_Id = @line_item_id
	   declare @temp_issue_status varchar (50)	
	   select  @temp_issue_status = isnull(max(cslit_status),'NOT ISSUED') from customer_shipping_line_item_table where cslit_status='ISSUED' and LineItem_Id = @line_item_id
	   
           declare @tempcount int
           select  @tempcount = count(cs.lineitem_id) from customer_shipping_line_item_table cs , customer_open_order_lineitem c 
           where c.cool_status ='APPROVED' and cs.lineitem_id = @line_item_id and cs.lineitem_id = c.lineitem_id
	   if( @tempcount = 0 )
	   begin
		--set @customer_partnumber = null
		set @label_number = null
		--select @customer_partnumber = customer_partnumber from Customer_PartNumbers where product_id = @temp_product_id and Customer_ID = @customer_id
		select @label_number = Label_Number from Customer_PartNumbers where product_id = @temp_product_id and Customer_ID = @customer_id and ODM_Id = @odm_id
			
	   	insert  @result values ( @sales_order_id,@line_item_id , @cool_line_item_no, @cool_po_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
				@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
				@temp_cool_sugg_date_without_substitutables,@temp_cool_schedule_date ,@temp_cool_revised_date,
				@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,'APPROVED',@temp_issue_status,@customer_partnumber,@label_number,@cool_is_expedite, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1)
			
	   end
           else
      	   begin 
           	open @status_cursor		
        	fetch next from @status_cursor into @cslit_status1
        
		while @@fetch_status = 0
		begin
			--set @customer_partnumber = null
			set @label_number = null
			--select @customer_partnumber = customer_partnumber from Customer_PartNumbers where product_id = @temp_product_id and Customer_ID = @customer_id
			select @label_number = Label_Number from Customer_PartNumbers where product_id = @temp_product_id and Customer_ID = @customer_id and ODM_Id = @odm_id
				
			if( @cslit_status1 = 'ISSUED' )
          		begin
          			insert  @result values ( @sales_order_id,@line_item_id ,  @cool_line_item_no, @cool_po_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
				@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
				@temp_cool_sugg_date_without_substitutables,@temp_cool_schedule_date ,@temp_cool_revised_date,
				@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,'ISSUED',@temp_issue_status,@customer_partnumber,@label_number,@cool_is_expedite, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1)
		        end
       			if( @cslit_status1 = 'CANCELLED' )
         		begin
         			insert  @result values ( @sales_order_id,@line_item_id , @cool_line_item_no, @cool_po_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
				@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
				@temp_cool_sugg_date_without_substitutables,@temp_cool_schedule_date ,@temp_cool_revised_date,
				@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,'APPROVED',@temp_issue_status, @customer_partnumber,@label_number,@cool_is_expedite, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1)
		        end
       			if( @cslit_status1 = 'SHIPPED' )
         		begin
         			insert  @result values ( @sales_order_id,@line_item_id , @cool_line_item_no,@cool_po_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
				@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
				@temp_cool_sugg_date_without_substitutables,@temp_cool_schedule_date ,@temp_cool_revised_date,
				@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,'PARTIALLY SHIPPED',@temp_issue_status,@customer_partnumber,@label_number,@cool_is_expedite, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1)
				break
       			end
         		fetch next from @status_cursor into @cslit_status1  
       		end --status_cursor while
   		close @status_cursor
		deallocate @status_cursor
          end --else      
         	fetch next from @approved_orders_cursor into @sales_order_id,@line_item_id , @cool_line_item_no, @cool_po_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
		@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,@temp_cool_sugg_date_without_substitutables,
		@temp_cool_schedule_date ,@temp_cool_revised_date, @temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,
		@sales_order_no,@cool_is_expedite, @customer_partnumber, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1
		--select @customer_partnumber = customer_partnumber from Customer_PartNumbers where product_id = @temp_product_id and Customer_ID = @customer_id
		select @label_number = Label_Number from Customer_PartNumbers where product_id = @temp_product_id and Customer_ID = @customer_id and ODM_Id = @odm_id
      end	--approved_orders_cursor while
	
	close @approved_orders_cursor
	deallocate @approved_orders_cursor
    		
    insert @fn_result select * from @result order by LineItem_Id
	
  return 
END



